package org.HisCanvas.ChrisRiffle.JFlightLog.persist;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import org.HisCanvas.ChrisRiffle.JFlightLog.common.LogBookUtils;
import org.HisCanvas.ChrisRiffle.JFlightLog.dataModel.LogBook;
import org.HisCanvas.ChrisRiffle.JFlightLog.dataModel.LogBookRec;
import org.HisCanvas.ChrisRiffle.JFlightLog.logging.LogController;

/**
 * Central point for data base operations(connecting, creating, updating,
 * querying, shutting down, etc.
 */
public class LogBookDaoJdbc implements LogBookDAO, LogBookDbConstants{
		
		
	//*************************************************************************
	//*************************************************************************
	//						CONSTANTS
	//*************************************************************************
	//*************************************************************************

	private static String COMMA = ",";
	
	private static String APOS = "\'";
	
	private static final String EQUALS = " = ";
	
	
	// db column names significant to db logbook table
	
	//*************************************************************************
	//*************************************************************************
	//						CONSTANTS
	//*************************************************************************
	//*************************************************************************
	


		
		
	/**
	 * Classpath to our HSQLDB file
	 */
	private static final String HSQLDB_DB_FILE_CLASSPATH = "jdbc:hsqldb:file:res/database/hsqldb/JFlightLogDb";	

	private static final String HSQLDB_DRIVER = "org.hsqldb.jdbcDriver";
		
	
	
	//*************************************************************************
	//*************************************************************************
	//						MEMBERS
	//*************************************************************************
	//*************************************************************************
	
	
	/**
	 * our connnection to the db - persist for life of program
	 */
	private static Connection dbConnection;
				
	
	/**
	 * Describes the SQL statement type
	 * @author RIF	 
	 */
	public static enum SQL_STATEMENT_TYPE{
		CREATE,
		INSERT,
		UPDATE,
		DELETE,
		DROP
	}
		

	// *************************************************************************
	// *************************************************************************
	// 					PRIVATE methods
	// *************************************************************************
	// *************************************************************************
	
	
	
	/**
	 * Connects to the database
	 */
	public void init() throws Exception {

		try {

			loadHsqldbDriver();
			
			initDbConnection();			

		} catch (Exception e) {
			
			//TODO FIXME THROW FATAL ERROR
			e.printStackTrace();
		}
	}


	
	/**
	 * loadHsqldbDriver
	 */
	private void loadHsqldbDriver() {
		
		try {
	
			Class.forName(HSQLDB_DRIVER);
	
		} catch (Exception e) {
	
			e.printStackTrace();
		}
	}
	
	
	
	/**
	 * Makes a jdbc connection to the local database
	 * 
	 */
	private void initDbConnection() {
	
		LogController.info("initDbConnection...");
		
		try {
				
			if (dbConnection != null && !dbConnection.isClosed()) {
				
				// we're already connected!
				return;
			}
			
			dbConnection = DriverManager.getConnection(HSQLDB_DB_FILE_CLASSPATH,
					"sa", "");
	
			// successful connection, so return
			return;
	
		} catch (Exception e) {
	
			e.printStackTrace();
		}
	
	}


	// *************************************************************************
	// *************************************************************************
	// 					PUBLIC methods
	// *************************************************************************
	// *************************************************************************
	
	
	
	/**
	 * 
	 */
	private void testInsertDuplicateRecords(LogBook pLogBook) throws SQLException{
		
		for(LogBookRec iRec : pLogBook.getLogBookRecList()){
			
			iRec.setFlightDate(new java.util.Date());
			
			addLogBookRec(iRec);
			addLogBookRec(iRec);
			addLogBookRec(iRec);
			addLogBookRec(iRec);
		}
		
		
	}



	//	/**
	//	 * CONVENIENCE
	//	 * 
	//	 * Inserts a whole entire logbook into the database :) It does this by
	//	 * looping through all the {@link LogBookRecord}'s
	//	 */
	//	public static void insertWholeEntireLogBook(LogBook logBook) {
	//
	//		if (logBook == null) {
	//			//TODO FIXME LOG
	//			return;
	//		}
	//		
	//		for (LogBookRec logBookRec : logBook.getLogBookRecList()) {
	//			
	//			insertDbRec(logBookRec);
	//		}
	//	}
	
		
	

	/**
	 * getLogBookRec from the current index of the passed result set
	 * 
	 */
	private LogBookRec parseLogBookRecFromDb(ResultSet rSet) throws SQLException{
	
		if(rSet == null){
			
			throw new SQLException("DbController.getLogBookRec() was given a null ResultSet");			
		}
					
		LogBookRec rLogBookRec = new LogBookRec(rSet.getInt(PK_ID));									
		
		
		Date flightDate = rSet.getDate(DATE_OF_FLIGHT);
		rLogBookRec.setFlightDate(flightDate);
		
		
		String aircraftType = rSet.getString(AIRCRAFT_TYPE);						
		rLogBookRec.setAircraftType(aircraftType);

		
		String aircraftRegistration = rSet.getString(AIRCRAFT_REGISTRATION);
		rLogBookRec.setAircraftRegistration(aircraftRegistration);
		

		String departAirportCode = rSet.getString(DEPARTURE_AIRPORT_CODE);
		rLogBookRec.setDepartAirportCode(departAirportCode);
		
		
		String arrivalAirportCode = rSet.getString(ARRIVAL_AIRPORT_CODE);
		rLogBookRec.setArrivalAirportCode(arrivalAirportCode);
		
		
		Short landingsTotalCount = rSet.getShort(LANDINGS_TOTAL_COUNT);
		rLogBookRec.setLandingsTotalCount(landingsTotalCount);
		
		
		Short landingsDayCount = rSet.getShort(LANDINGS_DAY_COUNT);
		rLogBookRec.setLandingsDayCount(landingsDayCount);
										

		Short landingsNightCount = rSet.getShort(LANDINGS_NIGHT_COUNT);
		rLogBookRec.setLandingsNightCount(landingsNightCount);
		
		
		BigDecimal singleEngineHours = rSet.getBigDecimal(SINGLE_ENGINE_HOURS);
		rLogBookRec.setSingleEngineHours(singleEngineHours);
		
		
		BigDecimal multiEngineHours = rSet.getBigDecimal(MULTI_ENGINE_HOURS);
		rLogBookRec.setMultiEngineHours(multiEngineHours);
		
					
		BigDecimal xCountryHours = rSet.getBigDecimal(CROSS_COUNTRY_HOURS);
		rLogBookRec.setxCountryHours(xCountryHours);
		
		
		BigDecimal dayHours = rSet.getBigDecimal(DAY_HOURS);
		rLogBookRec.setDayHours(dayHours);
		
		
		BigDecimal nightHours = rSet.getBigDecimal(NIGHT_HOURS);
		rLogBookRec.setNightHours(nightHours);
		
								
		BigDecimal instrumentActualHours = rSet.getBigDecimal(INSTRUMENT_ACTUAL_HOURS);
		rLogBookRec.setInstrumentActualHours(instrumentActualHours);
								
		
		BigDecimal instrumentSimHours = rSet.getBigDecimal(INSTRUMENT_SIMULATED_HOURS);
		rLogBookRec.setInstrumentSimHours(instrumentSimHours);
		
								
		Short iapCount = rSet.getShort(INSTRUMENT_APPROACHES_COUNT);
		rLogBookRec.setIapCount(iapCount);
										

		String iapNames = rSet.getString(INSTRUMENT_APPROACHES_NAMES);
		rLogBookRec.setIapNames(iapNames);
		
		
		String safetyPilotName = rSet.getString(SAFETY_PILOT_NAME);
		rLogBookRec.setSafetyPilotName(safetyPilotName);
		
		
		BigDecimal simHours = rSet.getBigDecimal(SIMULATOR_HOURS);
		rLogBookRec.setSimHours(simHours);
		
		
		BigDecimal dualReceivedHours = rSet.getBigDecimal(DUAL_INSTRUCTION_RECEIVED_HOURS);
		rLogBookRec.setDualReceivedHours(dualReceivedHours);
								

		BigDecimal dualGivenHours = rSet.getBigDecimal(DUAL_INSTRUCTION_GIVEN_HOURS);
		rLogBookRec.setDualGivenHours(dualGivenHours);
		
		
		BigDecimal picHours = rSet.getBigDecimal(PILOT_IN_COMMAND_HOURS);
		rLogBookRec.setPicHours(picHours);
		
				
		BigDecimal  sicHours = rSet.getBigDecimal(SECOND_IN_COMMAND_HOURS);
		rLogBookRec.setSicHours(sicHours);
		
						
		BigDecimal soloHours = rSet.getBigDecimal(SOLO);
		rLogBookRec.setSoloHours(soloHours);

		BigDecimal totalHours = rSet.getBigDecimal(TOTAL_HOURS);
		rLogBookRec.setTotalHours(totalHours);
		
		
		String remarks = rSet.getString(REMARKS);
		rLogBookRec.setRemarks(remarks);
			
		return rLogBookRec;		
	}



	/**
	 * Executes a query sql statement
	 */
	private static ResultSet executeSqlQuery(String pSqlQuery) throws SQLException {
										
		// statement objects can be reused with
		// repeated calls to execute but we choose to make a new one each time
		Statement st = dbConnection.createStatement(); 
		
		ResultSet rs  = st.executeQuery(pSqlQuery); 				
				
		st.close(); 
		
		return rs;		
	}



	/**
	 * Executes SQL statements 
	 * note: is NOT synchronized, NOT intended for concurrency
	 * CRUD: create(&insert)/update/delete
	 */
	private static void executeCrudSql(String pSqlStatementString) 
		throws SQLException {
				
		Statement sqlStatement = dbConnection.createStatement();				
		
		System.out.println("executeCrudSql, statemtn = " + pSqlStatementString);
		
		int sqlStatementResult = sqlStatement.executeUpdate(pSqlStatementString); 
		
		
		
		if (sqlStatementResult == -1) {
			
			System.out.println("db error : " + pSqlStatementString);
			//TODO FIXME LOG IT AND ALERT THE USER
		}
		
		sqlStatement.close();
	}

		
		
	// *************************************************************************
	// *************************************************************************
	// 					PUBLIC methods
	// *************************************************************************
	// *************************************************************************
		

	/**
	 * Deletes all data from the logbook table
	 */
	public static void deleteAllFromLogBookTable() throws Exception {

		executeCrudSql("DELETE FROM " + LOGBOOK_TABLE);
	}

	
	/**
	 * Shuts down the database in a clean manner
	 */
	public void close() throws Exception {

		LogController.getLogger().info("close()");			
		
		Statement st = dbConnection.createStatement();
		
		st.execute("COMMIT");
		
		// db writes out to files and performs clean shuts down
		// otherwise there will be an unclean shutdown
		// when program ends
		st.execute("SHUTDOWN");		
		
		// if there are no other open connections
		dbConnection.close(); 
	}
	


	
	
	/**
	 * Gets the the entire logbook from the database 
	 */
	public LogBook getLogBook() throws SQLException{
		
		LogController.info("getLogBook()...");		
		
		ResultSet rSet = executeSqlQuery("SELECT * FROM " + LOGBOOK_TABLE);
		
		if(rSet == null){
			
			return null;
		}
		
		LogBook rLogBook = new LogBook();				
				
		while(rSet.next()){
			
			LogBookRec iLogBookrec = parseLogBookRecFromDb(rSet);
						
			rLogBook.getLogBookRecList().add(iLogBookrec);			
		}
		
		return rLogBook;						
	}


	/**
	 * removeLogBookRec
	 * 
	 * @param pPrimaryKey
	 * @throws SQLException
	 */
	public void removeLogBookRec(LogBookRec pLogBookRec) throws Exception{
		
		String deleteSql = "DELETE FROM " + LOGBOOK_TABLE + " WHERE " + PK_ID + " = " + pLogBookRec.getPrimaryKey();
		
		executeCrudSql(deleteSql);
	}
	
	
	
	/**
	 * Updates the specified logbookrec
	 * 
	 * @param pLogBookRec - the logbookrec to update
	 */
	public void modifyLogBookRec(LogBookRec pLogBookRec) throws SQLException{
		
		String updateSql = "UPDATE " + LOGBOOK_TABLE + " SET " + 			
			
			// convert the util Date into an Sql Date before putting it into the database
			DATE_OF_FLIGHT + EQUALS + APOS + new java.sql.Date(
					pLogBookRec.getFlightDate().getTime()) + APOS + COMMA +

			AIRCRAFT_TYPE + EQUALS + APOS + pLogBookRec.getAircraftType() + APOS + COMMA +

			AIRCRAFT_REGISTRATION + EQUALS + APOS + pLogBookRec.getAircraftRegistration() + APOS + COMMA +

			DEPARTURE_AIRPORT_CODE + EQUALS + APOS + pLogBookRec.getDepartAirportCode() + APOS + COMMA +

			ARRIVAL_AIRPORT_CODE + EQUALS + APOS + pLogBookRec.getArrivalAirportCode() + APOS + COMMA +

			LANDINGS_TOTAL_COUNT + EQUALS + pLogBookRec.getLandingsTotalCount() + COMMA +

			LANDINGS_DAY_COUNT + EQUALS + pLogBookRec.getLandingsDayCount() + COMMA +

			LANDINGS_NIGHT_COUNT + EQUALS + pLogBookRec.getLandingsNightCount() + COMMA +

			SINGLE_ENGINE_HOURS + EQUALS + pLogBookRec.getSingleEngineHours() + COMMA +

			MULTI_ENGINE_HOURS + EQUALS + pLogBookRec.getMultiEngineHours() + COMMA +

			CROSS_COUNTRY_HOURS + EQUALS + pLogBookRec.getxCountryHours() + COMMA +

			DAY_HOURS + EQUALS + pLogBookRec.getDayHours() + COMMA +

			NIGHT_HOURS + EQUALS + pLogBookRec.getNightHours() + COMMA +

			INSTRUMENT_ACTUAL_HOURS + EQUALS + pLogBookRec.getInstrumentActualHours() + COMMA +

			INSTRUMENT_SIMULATED_HOURS + EQUALS + pLogBookRec.getInstrumentSimHours() + COMMA +

			INSTRUMENT_APPROACHES_COUNT + EQUALS + pLogBookRec.getIapCount() + COMMA +

			INSTRUMENT_APPROACHES_NAMES + EQUALS + APOS + pLogBookRec.getIapNames() + APOS + COMMA +

			SAFETY_PILOT_NAME + EQUALS + APOS + pLogBookRec.getSafetyPilotName() + APOS + COMMA +

			SIMULATOR_HOURS + EQUALS + pLogBookRec.getSimHours() + COMMA +

			DUAL_INSTRUCTION_RECEIVED_HOURS + EQUALS + pLogBookRec.getDualReceivedHours() + COMMA +

			DUAL_INSTRUCTION_GIVEN_HOURS + EQUALS + pLogBookRec.getDualGivenHours() + COMMA +

			PILOT_IN_COMMAND_HOURS + EQUALS + pLogBookRec.getPicHours() + COMMA +

			SECOND_IN_COMMAND_HOURS + EQUALS + pLogBookRec.getSicHours() + COMMA +

			SOLO + EQUALS + pLogBookRec.getSoloHours() + COMMA +

			TOTAL_HOURS + EQUALS + pLogBookRec.getTotalHours() + COMMA +

			REMARKS + EQUALS + APOS + pLogBookRec.getRemarks() + APOS;
		
		
		executeCrudSql(updateSql);
		
	}
	
	
	/**
	 * Inserts the logBookRec parameter into the db logBook
	 * 
	 * @param pLogBookRec to insert into the db
	 */
	public void addLogBookRec(LogBookRec pLogBookRec) throws SQLException{
		
		if(pLogBookRec == null){
			
			throw new SQLException("DbController.insertLogBookRec, pLogBookRec is null");
		}
		
		/*
		 * Note we don't insert the primary key b/c it's an identity and the db 
		 * handles generating it automatically
		 */
		String insertStatement = "INSERT INTO logbook(" + 
						
			  DATE_OF_FLIGHT + COMMA +
			  
			  AIRCRAFT_TYPE+ COMMA +
			  
			  AIRCRAFT_REGISTRATION + COMMA +
			  
			  DEPARTURE_AIRPORT_CODE + COMMA +
			  
			  ARRIVAL_AIRPORT_CODE + COMMA +
			  
			  LANDINGS_TOTAL_COUNT + COMMA +
			  
			  LANDINGS_DAY_COUNT + COMMA +
			  
			  LANDINGS_NIGHT_COUNT + COMMA +
			  
			  SINGLE_ENGINE_HOURS + COMMA +
			  
			  MULTI_ENGINE_HOURS + COMMA +
			  
			  CROSS_COUNTRY_HOURS + COMMA +
			  
			  DAY_HOURS + COMMA +
			  
			  NIGHT_HOURS + COMMA +
			  
			  INSTRUMENT_ACTUAL_HOURS + COMMA +
			  
			  INSTRUMENT_SIMULATED_HOURS + COMMA +
			  
			  INSTRUMENT_APPROACHES_COUNT + COMMA +
			  
			  INSTRUMENT_APPROACHES_NAMES + COMMA +
			  
			  SAFETY_PILOT_NAME + COMMA +
			  
			  SIMULATOR_HOURS + COMMA +
			  
			  DUAL_INSTRUCTION_RECEIVED_HOURS + COMMA +
			  
			  DUAL_INSTRUCTION_GIVEN_HOURS + COMMA +
			  
			  PILOT_IN_COMMAND_HOURS + COMMA +
			  
			  SECOND_IN_COMMAND_HOURS + COMMA +
			  
			  SOLO + COMMA +
			  
			  TOTAL_HOURS + COMMA +
			  
			  REMARKS 
			  
			  + ") VALUES (" +
			  
			  // convert our util date into an sql date for the db
			  APOS + new java.sql.Date(pLogBookRec.getFlightDate().getTime()) + APOS + COMMA +
			  
			  APOS + pLogBookRec.getAircraftType() + APOS + COMMA +
			  
			  APOS + pLogBookRec.getAircraftRegistration() + APOS + COMMA +
			  			  
			  APOS + pLogBookRec.getDepartAirportCode() + APOS + COMMA +
			  
			  APOS + pLogBookRec.getArrivalAirportCode() + APOS + COMMA +
			  
			  pLogBookRec.getLandingsTotalCount() + COMMA +
			  
			  pLogBookRec.getLandingsDayCount() + COMMA +
			  
			  pLogBookRec.getLandingsNightCount() + COMMA +
			  
			  pLogBookRec.getSingleEngineHours() + COMMA +
			  
			  pLogBookRec.getMultiEngineHours() +  COMMA +
			  
			  pLogBookRec.getxCountryHours() +  COMMA +
			  
			  pLogBookRec.getDayHours() +  COMMA +
			  
			  pLogBookRec.getNightHours() +  COMMA +
			  
			  pLogBookRec.getInstrumentActualHours() + COMMA + 
			  
			  pLogBookRec.getInstrumentSimHours() +  COMMA +
			  
			  pLogBookRec.getIapCount() +  COMMA +
			  			  			  			  
			  APOS + pLogBookRec.getIapNames() + APOS + COMMA +
			  			  
			  APOS + pLogBookRec.getSafetyPilotName() + APOS + COMMA +
			  
			  pLogBookRec.getSimHours() + COMMA +
			  
			  pLogBookRec.getDualReceivedHours() + COMMA + 
			  
			  pLogBookRec.getDualGivenHours() +  COMMA +
			  
			  pLogBookRec.getPicHours() +  COMMA +
			  
			  pLogBookRec.getSicHours() +  COMMA +
			  
			  pLogBookRec.getSoloHours() +  COMMA +
			  
			  pLogBookRec.getTotalHours() +  COMMA +
			  
			  APOS + pLogBookRec.getRemarks() + APOS 
			  +")";
		
		executeCrudSql(insertStatement);
		
	}
	
	/**
	 * testUpdate
	 */
	private void testUpdate(LogBook pLogBook, String pSomethingFunny, Short pSomeNumber) throws SQLException{
		
		for(LogBookRec iLogBookRec: pLogBook.getLogBookRecList()){
			
			iLogBookRec.setAircraftType(pSomethingFunny);
			iLogBookRec.setAircraftRegistration(pSomethingFunny);
			iLogBookRec.setIapNames(pSomethingFunny);
			iLogBookRec.setSafetyPilotName(pSomethingFunny);
			iLogBookRec.setRemarks(pSomethingFunny);
			
			iLogBookRec.setLandingsDayCount(pSomeNumber);
			iLogBookRec.setLandingsNightCount(pSomeNumber);
			iLogBookRec.setIapCount(pSomeNumber);
						
			modifyLogBookRec(iLogBookRec);
		}	
	}
	
	/**
	 * tester
	 */
	public static void main(String[] args) throws Exception{
				
		LogBookDaoJdbc dao = new LogBookDaoJdbc();
						
		dao.initDbConnection();
						
		LogBook selectedLogBook = dao.getLogBook();
													
		String logBookString = LogBookUtils.toString(selectedLogBook);		
		
		LogController.info(logBookString);
		
//		LogBookRec recToRemove = selectedLogBook.getLogBookRecList().get(0);
//		removeLogBookRec(recToRemove.getPrimaryKey());
						
//		LogController.getLogger().info("* * * Testing Update * *  *");
				
//		Short s = 7;		
//		testUpdate(selectedLogBook, "hEyHeYhEy", s);
		
		dao.testInsertDuplicateRecords(selectedLogBook);
		
			
		dao.close();			
	}

	
	/**
	 * getLogBookRec
	 */
	@Override
	public LogBookRec getLogBookRec(int pPkId) throws Exception {

		LogController.info("getLogBookRec()...");	
		
		LogBookRec rLogBookRec = null;
		
		ResultSet rSet = executeSqlQuery("SELECT * FROM " + LOGBOOK_TABLE 
				+ " WHERE " + PK_ID + " = " +pPkId);
		
		if(rSet == null){
			
			return null;
		}
									
		while(rSet.next()){
			
			if(rLogBookRec != null){
				
				String errMsg = "pkId: " + pPkId + " not unique!";				
				LogController.severe(errMsg);
				throw new Exception(errMsg);
			}			
			
			rLogBookRec = parseLogBookRecFromDb(rSet);
						
		}
		
		return rLogBookRec;						
	}	
	
}



